package com.homework.ch02;

import java.io.BufferedReader;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStreamReader;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.SQLException;

/**
 * @author yuxi
 * @version 1.0
 * @date 2025/11/6
 * @Description:
 */
public class Test02 {
    private static String driverClass = "com.mysql.cj.jdbc.Driver";
    private static String url = "jdbc:mysql://localhost:3306/briup";
    private static String username = "briup";
    private static String password = "briup";
    public static void main(String[] args) {
        Connection conn = null;
        PreparedStatement pstmt = null;
        BufferedReader br = null;
        try {
            // 加载驱动程序
            Class.forName(driverClass);
            // 获取数据库连接
            conn = DriverManager.getConnection(url, username, password);
            conn.setAutoCommit(false);
            //清空数据
            String sql = "truncate t_customer";
            pstmt = conn.prepareStatement(sql);
            pstmt.executeUpdate();
            System.out.println("数据清空成功！");
            // 读取数据
            String sql1 = "insert into t_customer(id,name, age, birthday) values(?,?,?,?)";
            pstmt = conn.prepareStatement(sql1);
            // 读取文件
            br = new BufferedReader(new InputStreamReader(new FileInputStream("3-jdbc-demo/src/customer.txt")));
            String line = null;
            int count = 0;
            while ((line = br.readLine())!= null) {
                String[] data = line.split(":");
                // 向数据库插入数据
                pstmt.setInt(1, Integer.parseInt(data[0]));
                pstmt.setString(2, data[1]);
                pstmt.setInt(3, Integer.parseInt(data[2]));
                pstmt.setString(4, data[3]);
                pstmt.addBatch();
                count++;
                if (count % 3 == 0) {
                    pstmt.executeBatch();
                    System.out.println("已插入" + count + "条数据");
                }
            }
            if (count % 3 != 0) {
                pstmt.executeBatch();
                System.out.println("已插入" + count + "条数据");
            }
            conn.commit();
            System.out.println("数据插入成功,共"+count+"条数据");

        } catch (SQLException e) {
            try {
                if (conn != null) conn.rollback();
                System.out.println("数据插入失败，事务回滚");
            } catch (SQLException ex) {
                ex.printStackTrace();
            }
            e.printStackTrace();
        } catch (ClassNotFoundException | IOException e) {
            e.printStackTrace();
        } finally {
            if (br != null) {
                try {
                    br.close();
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
            if (pstmt != null) {
                try {
                    pstmt.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            if (conn != null) {
                try {
                    conn.close();
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
        }
    }

}
